libname libone 'C:\Users\m40lxz1\OneDrive - Northern Illinois University\quality\sas\sasdata\stability';
 /* This program reports the average number of annual rating changes (totalup, totaldown, totalchange) per bond-year, the percentage   */
/* of bond-year with rating upgrades (up) downgrades (down), and rating change (change), and the number of rating upgrade, downgrade   */
/* and changes (num_up, num_down, um_change), conditional on the occurance of such event, and finally percentage of bond-year where    */
/* an rating action is reversed within the year (reverse)                                                                               */


data ejr;
 set libone.ejr_annual_ratingchange;
 if totalup gt 0 then up=1; else up=0;																* totalup, totaldown and totalchange are the number of upgrades,downgrades, and rating changes within the calendar year;
 if totaldown gt 0 then down=1; else down=0;														* up, down and change are dummy variables for year with at least one rating upgrade, downgrade or rating change;
 if totalchange gt 0 then change=1; else change=0;
 if totalup gt 0 then num_up=totalup;																* num_up, num_down and num)change are the number of rating up/downgrade/changes conditional on the occurance of the event;	
 if totaldown gt 0 then num_down=totaldown;
 if totalchange gt 0 then num_change=totalchange;
 if totalup gt 0 and totaldown gt 0 then reverse=1; 												*reverse is a dummy variable for year where there are both upgrade and downgrade;	
 else if totalchange gt 0 then reverse=0;															*It is conditional that the bond issue has at least one rating change in a given year;
 if year lt 2013 or year ge 2021 then delete;
 ran='EJR  ';
run;

proc sort data=ejr;
 by year;

proc means data=ejr;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;				*up*num_up=totalup, etc; 
 *by year;
run;



data mdy;
 set libone.mdy_annual_ratingchange;
  if totalup gt 0 then up=1; else up=0;
 if totaldown gt 0 then down=1; else down=0;
 if totalchange gt 0 then change=1; else change=0;
 if totalup gt 0 then num_up=totalup;
 if totaldown gt 0 then num_down=totaldown;
 if totalchange gt 0 then num_change=totalchange;
  if totalup gt 0 and totaldown gt 0 then reverse=1; 
 else if totalchange gt 0 then reverse=0;
  if year lt 2013 or year ge 2021 then delete;
  ran='MDY  '; 


proc sort data=mdy;
 by year;

proc means data=mdy;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
 *by year;run;

data sp;
 set libone.sp_annual_ratingchange;
   if totalup gt 0 then up=1; else up=0;
 if totaldown gt 0 then down=1; else down=0;
 if totalchange gt 0 then change=1; else change=0;
 if totalup gt 0 then num_up=totalup;
 if totaldown gt 0 then num_down=totaldown;
 if totalchange gt 0 then num_change=totalchange;
  if totalup gt 0 and totaldown gt 0 then reverse=1; 
 else if totalchange gt 0 then reverse=0;
  if year lt 2013 or year ge 2021 then delete;
  ran='SP   '; 


proc sort data=sp;
 by year;

proc means data=sp n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
*by year;run;


data fitch;
 set libone.fitch_annual_ratingchange;
   if totalup gt 0 then up=1; else up=0;
 if totaldown gt 0 then down=1; else down=0;
 if totalchange gt 0 then change=1; else change=0;
 if totalup gt 0 then num_up=totalup;
 if totaldown gt 0 then num_down=totaldown;
 if totalchange gt 0 then num_change=totalchange;
  if totalup gt 0 and totaldown gt 0 then reverse=1; 
 else if totalchange gt 0 then reverse=0;
  if year lt 2013 or year ge 2021 then delete;
  ran='Fitch'; 


proc sort data=fitch;
 by year;

proc means data=fitch n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year; run;

/**************************************************************************************************************/
data all;
 set ejr mdy sp fitch;run;

proc freq data=all;
 table ran;run;

ods graphics off;

proc ttest data=all;
 class ran;
 var up down change num_up num_down num_change reverse;
 where ran='EJR' or ran='Fitch';run;




/****************************************************************************************************************/

* The next two steps get the MDY rating change information for SP-MDY joint rated issues; 

proc sql;
 create table mdy_sp as
 select A.*
 from mdy as A
 inner join sp as B
 on A.id=B.id and A.year=B.year;

  title 'MDY rating change for SP-MDY joint rated';

 proc means data=mdy_sp n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;


* The next two steps get the SP rating change information for SP-MDY joint rated issues; 
 
proc sql;
 create table sp_mdy as
 select A.*
 from sp as A
 inner join mdy as B
 on A.id=B.id and A.year=B.year;

  title 'SP rating change for SP-MDY joint rated';

 proc means data=sp_mdy n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;

data mdy_sp_joint;
 set mdy_sp sp_mdy;

 ods graphics off;

proc ttest data=mdy_sp_joint;														* simple t test without clustered standard error correction;
 class ran;
 var up down change num_up num_down num_change reverse;

 run;

data mdy_sp_joint;
 set mdy_sp_joint;
 if ran='SP' then sp=1; else sp=0;run;

proc genmod data=mdy_sp_joint;
 class id;
 model reverse=sp /dist=normal;
 repeated sub=id/type=ind;
 run;
																			* the new few steps perform a paired t test to correct for clustering at bond level;
data mdy_sp;
 set mdy_sp;
 rename up=mdy_up down=mdy_down change=mdy_change  num_up=mdy_num_up num_down=mdy_num_down reverse=mdy_reverse; 

data sp_mdy;
 set sp_mdy;
 rename up=sp_up down=sp_down change=sp_change  num_up=sp_num_up num_down=sp_num_down reverse=sp_reverse; 


proc sort data=mdy_sp; by id year;
proc sort data=sp_mdy; by id year;

data mdy_sp_merged;
 merge mdy_sp sp_mdy;
 by id year;
run; 

proc ttest data=mdy_sp_merged;
 paired mdy_down*sp_down mdy_change*sp_change mdy_num_up*sp_num_up mdy_num_down*sp_num_down mdy_reverse*sp_reverse;run;

* The next two steps get the SP rating change information for SP-Fitch joint rated issues; 
 
proc sql;
 create table sp_fitch as
 select A.*
 from sp as A
 inner join fitch as B
 on A.id=B.id and A.year=B.year;

 title 'SP rating change for SP-Fitch joint rated';

 proc means data=sp_fitch n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;

* The next two steps get the Fitch rating change information for SP-Fitch joint rated issues; 
 
proc sql;
 create table fitch_sp as
 select A.*
 from fitch as A
 inner join sp as B
 on A.id=B.id and A.year=B.year;

 title 'Fitch rating change for SP-Fitch joint rated';

 proc means data=fitch_sp n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;


data fitch_sp_joint;
 set fitch_sp sp_fitch;

 ods graphics off;

proc ttest data=fitch_sp_joint;
 class ran;
 var up down change num_up num_down num_change reverse;

 run;

data fitch_sp_joint;
 set fitch_sp_joint;
 if ran='SP' then sp=1; else sp=0;run;

proc genmod data=fitch_sp_joint;
 class id;
 model num_change=sp /dist=normal;
 repeated sub=id/type=ind;
 run;


																			* the new few steps perform a paired t test to correct for clustering at bond level;
data fitch_sp;
 set fitch_sp;
 rename up=fitch_up down=fitch_down change=fitch_change  num_up=fitch_num_up num_down=fitch_num_down reverse=fitch_reverse; 

data sp_fitch;
 set sp_fitch;
 rename up=sp_up down=sp_down change=sp_change  num_up=sp_num_up num_down=sp_num_down reverse=sp_reverse; 


proc sort data=fitch_sp; by id year;
proc sort data=sp_fitch; by id year;

data fitch_sp_merged;
 merge fitch_sp sp_fitch;
 by id year;
run; 

proc ttest data=fitch_sp_merged;
 paired fitch_up*sp_up fitch_down*sp_down fitch_change*sp_change fitch_num_up*sp_num_up fitch_num_down*sp_num_down fitch_reverse*sp_reverse;run;
run;



* The next two steps get the Fitch rating change information for MDY-Fitch joint rated issues; 
 
proc sql;
 create table fitch_mdy as
 select A.*
 from fitch as A
 inner join mdy as B
 on A.id=B.id and A.year=B.year;

 title 'Fitch rating change for MDY-Fitch joint rated';

 proc means data=fitch_mdy n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;


* The next two steps get the MDY rating change information for MDY-Fitch joint rated issues; 
proc sql;
 create table mdy_fitch as
 select A.*
 from mdy as A
 inner join fitch as B
 on A.id=B.id and A.year=B.year;

 title 'Moody rating change for MDY-Fitch joint rated';

 proc means data=mdy_fitch n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run; 


data mdy_fitch_joint;
 set mdy_fitch fitch_mdy;

 ods graphics off;

proc ttest data=mdy_fitch_joint;
 class ran;
 var up down change num_up num_down num_change reverse;

 run;


data mdy_fitch_joint;
 set mdy_fitch_joint;
 if ran='MDY' then MDY=1; else MDY=0;run;

proc genmod data=mdy_fitch_joint;
 class id;
 model reverse=MDY /dist=normal;
 repeated sub=id/type=ind;
 run;
 																			* the new few steps perform a paired t test to correct for clustering at bond level;
data mdy_fitch;
 set mdy_fitch;
 rename up=mdy_up down=mdy_down change=mdy_change  num_up=mdy_num_up num_down=mdy_num_down reverse=mdy_reverse; 

data fitch_mdy;
 set fitch_mdy;
 rename up=fitch_up down=fitch_down change=fitch_change  num_up=fitch_num_up num_down=fitch_num_down reverse=fitch_reverse; 


proc sort data=mdy_fitch; by id year;
proc sort data=fitch_mdy; by id year;

data mdy_fitch_merged;
 merge mdy_fitch fitch_mdy;
 by id year;
run; 

proc ttest data=mdy_fitch_merged;
 paired mdy_up*mdy_down mdy_down*fitch_down mdy_change*fitch_change mdy_num_up*fitch_num_up mdy_num_down*fitch_num_down mdy_reverse*fitch_reverse;run;

run;

* The next two steps get the EJR rating change information for EJR-MDY joint rated issues; 
 
proc sql;
 create table EJR_MDY as
 select A.*
 from EJR as A
 inner join mdy as B
 on A.id=B.id and A.year=B.year;

 title 'EJR rating change for EJR-MDY joint rated';

 proc means data=EJR_MDY n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;



* The next two steps get the MDY rating change information for EJR-MDY joint rated issues; 
 
proc sql;
 create table MDY_EJR as
 select A.*
 from MDY as A
 inner join EJR as B
 on A.id=B.id and A.year=B.year;

 title 'MDY rating change for EJR-MDY joint rated';

 proc means data=MDY_EJR n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;

data mdy_ejr_joint;
 set mdy_ejr ejr_mdy;

 ods graphics off;

proc ttest data=mdy_ejr_joint;
 class ran;
 var up down change num_up num_down num_change reverse;

 run;

data mdy_ejr_joint;
 set mdy_ejr_joint;
 if ran='MDY' then MDY=1; else MDY=0;run;

proc genmod data=mdy_ejr_joint;
 class id;
 model num_change=MDY /dist=normal;
 repeated sub=id/type=ind;
 run;

 run;
																			* the new few steps perform a paired t test to correct for clustering at bond level;
data mdy_ejr;
 set mdy_ejr;
 rename up=mdy_up down=mdy_down change=mdy_change  num_up=mdy_num_up num_down=mdy_num_down reverse=mdy_reverse; 

data ejr_mdy;
 set ejr_mdy;
 rename up=ejr_up down=ejr_down change=ejr_change  num_up=ejr_num_up num_down=ejr_num_down reverse=ejr_reverse; 


proc sort data=mdy_ejr; by id year;
proc sort data=ejr_mdy; by id year;

data mdy_ejr_merged;
 merge mdy_ejr ejr_mdy;
 by id year;
run; 

proc ttest data=mdy_ejr_merged;
 paired mdy_up*ejr_up mdy_down*ejr_down mdy_change*ejr_change mdy_num_up*ejr_num_up mdy_num_down*ejr_num_down mdy_reverse*ejr_reverse;run;

run;



* The next two steps get the EJR rating change information for EJR-SP joint rated issues; 
 
proc sql;
 create table EJR_SP as
 select A.*
 from EJR as A
 inner join SP as B
 on A.id=B.id and A.year=B.year;

 title 'EJR rating change for EJR-SP joint rated';

 proc means data=EJR_SP n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;



* The next two steps get the SP rating change information for EJR-MDY joint rated issues; 
 
proc sql;
 create table SP_EJR as
 select A.*
 from SP as A
 inner join EJR as B
 on A.id=B.id and A.year=B.year;

 title 'SP rating change for EJR-SP joint rated';

 proc means data=SP_EJR n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;


data SP_ejr_joint;
 set SP_ejr ejr_sp;

 ods graphics off;

proc ttest data=Sp_ejr_joint;
 class ran;
 var up down change num_up num_down num_change reverse;

 run;


data sp_ejr_joint;
 set sp_ejr_joint;
 if ran='EJR' then EJR=1; else EJR=0;run;

proc genmod data=sp_EJR_joint;
 class id;
 model reverse=EJR /dist=normal;
 repeated sub=id/type=ind;
 run;
																			* the new few steps perform a paired t test to correct for clustering at bond level;
data sp_ejr;
 set sp_ejr;
 rename up=sp_up down=sp_down change=sp_change  num_up=sp_num_up num_down=sp_num_down reverse=sp_reverse; 

data ejr_sp;
 set ejr_sp;
 rename up=ejr_up down=ejr_down change=ejr_change  num_up=ejr_num_up num_down=ejr_num_down reverse=ejr_reverse; 


proc sort data=sp_ejr; by id year;
proc sort data=ejr_sp; by id year;

data sp_ejr_merged;
 merge sp_ejr ejr_sp;
 by id year;
run; 

proc ttest data=sp_ejr_merged;
 paired sp_up*ejr_up sp_down*ejr_down sp_change*ejr_change sp_num_up*ejr_num_up sp_num_down*ejr_num_down sp_reverse*ejr_reverse;run;

run;


* The next two steps get the EJR rating change information for EJR-Fitch joint rated issues; 
 
proc sql;
 create table EJR_Fitch as
 select A.*
 from EJR as A
 inner join Fitch as B
 on A.id=B.id and A.year=B.year;

 title 'EJR rating change for EJR-SP joint rated';

 proc means data=EJR_Fitch n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;



* The next two steps get the Fitch rating change information for EJR-Fitch joint rated issues; 
 
proc sql;
 create table Fitch_EJR as
 select A.*
 from Fitch as A
 inner join EJR as B
 on A.id=B.id and A.year=B.year;

 title 'Fitch rating change for EJR-SP joint rated';

 proc means data=Fitch_EJR n mean median std min max;
 var totalup totaldown totalchange up down change num_up num_down num_change reverse;
* by year;run;


data fitch_ejr_joint;
 set fitch_ejr ejr_fitch;

 ods graphics off;

proc ttest data=fitch_ejr_joint;
 class ran;
 var up down change num_up num_down num_change reverse;


data fitch_ejr_joint;
 set fitch_ejr_joint;
 if ran='EJR' then EJR=1; else EJR=0;run;

proc genmod data=fitch_EJR_joint;
 class id;
 model num_change=EJR /dist=normal;
 repeated sub=id/type=ind;
 run;


 																			* the new few steps perform a paired t test to correct for clustering at bond level;
data fitch_ejr;
 set fitch_ejr;
 rename up=fitch_up down=fitch_down change=fitch_change  num_up=fitch_num_up num_down=fitch_num_down reverse=fitch_reverse; 

data ejr_fitch;
 set ejr_fitch;
 rename up=ejr_up down=ejr_down change=ejr_change  num_up=ejr_num_up num_down=ejr_num_down reverse=ejr_reverse; 


proc sort data=fitch_ejr; by id year;
proc sort data=ejr_fitch; by id year;

data fitch_ejr_merged;
 merge fitch_ejr ejr_fitch;
 by id year;
run; 

proc ttest data=fitch_ejr_merged;
 paired fitch_up*ejr_up fitch_down*ejr_down fitch_change*ejr_change fitch_num_up*ejr_num_up fitch_num_down*ejr_num_down fitch_reverse*ejr_reverse;run;

run;
 run;
